create database dws;
use dws;

create table dws.dws_intention_customer_type_hour_i(
    customer_id int COMMENT '客户id',
    create_date_time STRING COMMENT '创建时间',
    year_date STRING COMMENT '年',
    month_date STRING COMMENT '月',
    day_date STRING COMMENT '日',
    hour_date STRING COMMENT '时',
    origin_type STRING COMMENT '数据来源',
    clue_state STRING COMMENT '线索状态',
    area string comment  '地区',
    itcast_subject_id int COMMENT '学科Id',
    itcast_subject_name string COMMENT '学科名称',
    itcast_school_id int COMMENT '校区Id',
    itcast_school_name string COMMENT '校区名称',
    tdepart_id INT COMMENT '直属部门',
    tdepart_name string COMMENT '部门名称'
)comment '客户意向类型时表'
PARTITIONED BY(dt STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

create table dws.dws_clue_customer_hour_i(
    customer_relationship_id int COMMENT '客户关系id',
    create_date_time STRING COMMENT '创建时间',
    year_date STRING COMMENT '年',
    month_date STRING COMMENT '月',
    day_date STRING COMMENT '日',
    hour_date STRING COMMENT '时',
    origin_type STRING COMMENT '数据来源',
    clue_state STRING COMMENT '线索状态'
)comment '客户线索时表'
PARTITIONED BY(dt STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');


insert overwrite table dws.dws_intention_customer_type_hour_i partition(dt)
select
    cr.customer_id,cr.create_date_time,cr.year_date,cr.month_date,cr.day_date,cr.hour_date,
    cr.origin_type,cc.clue_state,c.area,cr.itcast_subject_id,sj.name,cr.itcast_school_id,sc.name,
    ed.tdepart_id,ed.name,cr.create_date_time
from dwd.dwd_intention_customer_relationship_i cr
left join dwd.dwd_intention_customer_clue_i cc on cr.id = cc.customer_relationship_id
left join dim.dim_customer_i c on cr.customer_id = c.id
left join dim.dim_itcast_subject_i sj on cr.itcast_subject_id = sj.id
left join dim.dim_itcast_school_i sc on cr.itcast_school_id = sc.id
left join dim.dim_employee_department_i ed on cr.creator = ed.id;

insert overwrite table dws.dws_clue_customer_hour_i partition (dt)
select
cc.customer_relationship_id,
cr.create_date_time,cr.year_date,cr.month_date,cr.day_date,cr.hour_date,
cr.origin_type,cc.clue_state,cr.create_date_time
from dwd.dwd_intention_customer_clue_i cc
left join dwd.dwd_intention_customer_relationship_i cr on cc.customer_relationship_id = cr.id
where cc.clue_state = 1 and cc.customer_relationship_id not in (
select customer_relationship_first_id
from dwd.dwd_clue_customer_appeal_i ca
where ca.appeal_status = 1 and ca.customer_relationship_first_id != 0 )
and cr.origin_type = 1;
